﻿use persons
go
------------------6----------------------------------------------------------------
/*Find all father's contacts*/
SELECT 
[Person].[ID],
[Person].[FirstName], 
[Person].[LastName], 
[Contact].[ContactType], 
[Contact].[ContactValue] 
FROM [dbo].[Contact],[dbo].[Person] 
WHERE [Contact].[fk_personId]=[Person].[ID] AND [Contact].[fk_personId] IN(SELECT 
[Person].[ID] 
FROM [dbo].[Person]
WHERE [Person].[ID] IN (SELECT DISTINCT 
[Person].[fk_fatherId] 
FROM [dbo].[Person]))
ORDER BY  [Person].[ID]

SELECT 
[Person].[ID],
[Person].[FirstName], 
[Person].[LastName], 
[Contact].[ContactType], 
[Contact].[ContactValue] 
FROM [dbo].[Contact], [dbo].[Person] 
WHERE [Contact].[fk_personId]=[Person].[ID] AND [Contact].[fk_personId] IN(SELECT DISTINCT 
[Person].[ID] 
FROM [dbo].[Person], [dbo].[Person] AS [FATHER] 
WHERE [Person].[ID]=[FATHER].[fk_fatherId])
ORDER BY  [Person].[ID]

SELECT 
[Person].[ID],
[Person].[FirstName], 
[Person].[LastName], 
[Contact].[ContactType], 
[Contact].[ContactValue] 
FROM [dbo].[Contact] INNER JOIN [dbo].[Person] 
ON [Contact].[fk_personId]=[Person].[ID] 
WHERE  [Contact].[fk_personId] IN(SELECT DISTINCT 
[Person].[ID] 
FROM [dbo].[Person] INNER JOIN [dbo].[Person] AS [FATHER]
ON [Person].[ID]=[FATHER].[fk_fatherId])
ORDER BY  [Person].[ID]
